Consider a situation where, as a DBA, a new database falls under your care. Having implemented some monitoring, you find that the log has grown massively in size, is almost full, and that there isn’t the capacity on its disk drive to accommodate an urgent database maintenance operation.

What next? The reaction to avoid is panic, with resort to one of the many quick but dangerous ways to empty the log file, that appear on various online forums. We need to find a way to allow the maintenance process to proceed, we need to find out why the log grew so large and fix the issue and then we need to reduce the size of the log, in the process removing any internal fragmentation. We need to do so in a way that preserves the integrity of the log chain, which we may need subsequently for a database restore operation.

Explosive Log Growth

For demonstration purposes, we’ll create a simple MyMessages database, as shown in Listing 1. Notice that we specify a small initial log file (2 MB), which is set to grow in small increments (2 MB). We set the database to FULL recovery model and perform a full database backup (remember that FULL recovery model becomes operation only after the first full database backup). Finally, we create a simple Messages table.

Listing 1: Creating the MyMessages database and Messages table

Next, we’ll simulate some transactional activity on the Messages table, by inserting a row into the Messages table and then updating it in an explicit transaction, without committing or rolling back. Open a new tab in SSMS and execute Listing 2.

Listing 2: An uncommitted transaction on the Messages table

Back in the original tab, we’ll proceed to insert 1.3 million rows into Messages.

Listing 3: Insert 1.3 million rows into Messages

Let’s assume, obviously without any prior knowledge of what’s gone before, that this is the point that the DBA receives news of the problem. From log size and space usage data, we find that the log file for the MyMessages database is 836 MB in size and close to full.

Listing 4: Log size and space usage stats for MyMessages

Making Room in the Log

The first priority is to try to make some room in the log so that it doesn’t fill up completely, and so that the maintenance process can proceed. We try a log backup but for reasons that require further investigation, SQL Server will not truncate the log.

Listing 5: Log backup does not truncate the log

In order to buy some time, and stave off further problems, we decide to create more space in the log by adding a 3 GB secondary log file, on a separate disk.

Listing 6: Adding a secondary 3 GB log file

The planned database maintenance operation can now proceed and we can turn our attention to finding out what caused the log to grow so large, and why SQL Server won’t truncate it.

Why doesn’t SQL Server truncate the log?

For a FULL or BULK_LOGGED recovery model database, only a log backup will result in truncation of the log (i.e. enable reuse of space in the log). When a log backup occurs, SQL Server can reuse the space in any portions of the log that are marked as “inactive”. The oldest log record (referred to as the MinLSN record) that is still required for a successful database wide rollback, or is still required by another activity or operation in the database marks the start of the active log. The MinLSN log record could be the one marking the start of the oldest open transaction, or the oldest log record still required for a log backup, or the oldest log record still required by another database process, such as database mirroring or transactional replication (see Why is my Transaction Log Full? for more details).

This explains why a long-running uncommitted transaction or an application leaving “orphaned transactions” in the database can keep large areas of the log “active” and so prevent log truncation. In this case, we’ve contrived to leave an uncommitted transaction in the database and so can simply commit it or roll it back. To simulate this, return to listing 2, and roll back the open transaction.

Next, we can take another log backup and it truncates the log, and creates plenty of reusable space in the primary log file.

Listing 7: A second log backup, after clearing the open transaction, truncates the log

So far so good: we’ve allowed the maintenance operation to proceed, and we’ve fixed the problem that caused the explosive log growth. However, we still have two outstanding problems:

• A database with multiple log files
• A database with a principal log file that is bloated and likely highly fragmented

Remove any Secondary log files

We need to get rid of that secondary log file as soon as possible. SQL Server does not write log records in parallel to multiple log files, even when created on separate drives, so there is no performance advantage to having multiple log files. As soon as an “emergency” secondary log file is no longer required, we need to remove it, as all it will do is slow down any restore or recovery operations, since SQL Server has to zero-initialize the log during full and differential restore operations.

To see the impact this can have, let’s leave our secondary log file in place and perform a restore on the MyMessages database.

Listing 8: Restoring MyMessages (with secondary log file)

The restore took over 50 s. If we repeat the exact same steps, but without adding the secondary log file, the comparative restore, in our tests, took about 6 seconds. This is a substantial impact, even for a relatively modestly size secondary log. In cases where the log file is much larger, the effect on backup times can be dramatic.

In order to remove the secondary log file, we need to wait until it contains no part of the active log. Since our goal is to remove it, it’s permissible to shrink this secondary log file (demonstrated shortly), and turn off auto-growth for this file. Shrinking the secondary log to zero will return it to its original size (500 MB) and so “encourage” the active log to move swiftly back into the primary log file. It’s important to note that this will not move any log records in the secondary log over to the primary log (some people expect this behavior because if we specify the EMPTYFILE parameter, when shrinking a data file, SQL Server will move the data to another data file in the same filegroup).

As soon as a log backup means that the secondary log file contains no part of the active log, we can simply remove it.

Listing 9: Removing the secondary log file

Shrink Primary Log and Remove Fragmentation

When we first created the MyMessages database, we sized the primary log file at just 2 MB and allowed SQL Server to auto-grow it in 2MB increments to over 800 MB in size. A transaction log that auto-grows frequently, in small increments, will have a very large number of small Virtual Log Files (VLFs). This phenomenon is log fragmentation. If a database process, such as the crash recovery process, has to read the log file, it starts by reading in all the VLFs. If there are many of them, this will be a longer operation, and may affect the overall time taken to recover the database. A similar argument applies to other operations that read the log, such as log backups.

Essentially, the initial size and relatively small growth increments we’ve chosen for this database are inappropriate for this sort of data load and lead to the creation of a large number of VLFs. We can confirm this by interrogating the VLF architecture using a command called DBCCLogInfo, as shown in Listing 8.

Listing 10: A fragmented log!

We’re not going to discuss the output of DBCC LogInfo in any detail. All we’re interested in at this stage is that DBCC LogInfo returns 1672 rows, meaning 1672 VLFs.

Note that when we created the 3 GB secondary log file, but this time setting a reasonable initial size (500 MB), followed by a manual growth, the secondary log file comprised only 24 new VLFs, a very reasonable number of virtual files.

The way to reduce the primary log file to a reasonable size, and remove the fragmentation, is to shrink it, and then manually resize it. Note that we should never shrink the log as part of our standard maintenance operations, as it will simply need to grow again as we add more data, and modify our existing data, and these log growth events are expensive, since SQL Server has to zero-initialize the new log space.

However, shrinking the log is permissible in situations such as this, in the knowledge that we have investigated and resolved the cause of the excessive log growth, and will then correctly size the log such that shrinking the log should be a “one off” event.

The recommended approach is use DBCC SHRINKFILE (see http://msdn.microsoft.com/en-us/library/ms189493.aspx) to reclaim the space, and remove fragmentation. If we specify 0 (zero) as the target size, or don’t specify a size, SQL Server will attempt to shrink the log back to its initial size. Alternatively, we can specify a target size to which to shrink the file (such as “1”, if we wish SQL Server to order shrink the log to its smallest possible size). In Listing 11, we use zero in order to shrink the log back to its initial size (2 MB).

Listing 11: Shrinking the primary log file (partial success)

In the output from this command, we see the current database size (91008*8-KB pages) and minimum possible size after shrinking (256*8-KB pages). This is actually an indication that the shrink did not work fully. SQL Server shrank the log to the point where the last VLF in the file contained part of the active log and then stopped. Check the messages tab for confirmation.

Perform a log backup and try again.

Listing 12: Shrinking the primary log file after log backup

Having done this, we can now manually grow the log to the required size, in a similar way to that demonstrated in Listing 6.

Summary

If a log file grows excessively, and you need to add a temporary secondary log file, remove it as soon as it is no longer required. If you leave the secondary log file in place, it may slow down considerably database restore and recovery operations.

It is a bad idea to undersize the transaction log, and then let SQL Server auto-grow it in an uncontrolled fashion, in small increments. This can lead to log fragmentation, which may slow down log backup and database recovery operations. The way to avoid issues relating to expensive log growth events, and log fragmentation, is simply to set the correct initial size for the log (and data) file, allowing for current requirements, and predicted growth over a set period.

Ideally, having done this, the log would never auto-grow, which isn’t to say that we should disable the auto-growth facility. It must be there as a safety mechanism, and we should set a reasonable auto-growth increment in order to avoid growth events fragmenting the log. However, having sized the log appropriately, we are not relying on auto-growth being the mechanism that controls log growth.

If you’d like to learn more about how to build a “bullet-proof” backup and restore strategy for your databases, and optimize your transaction log management, check out the following resources:

Bulletproof your Database Backup and Recovery Strategy (free whitepaper)

SQL Server Backup and Restore, by Shawn McGehee (free eBook)

SQL Server Transaction Log Management, by Tony Davis and Gail Shaw (Paperback)